﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using MockPrjTeam2.Entity;
using MockPrjTeam2.Common;

namespace MockPrjTeam2.DAO
{
    public class AddressDao
    {                
        public List<Address> getAddressesByKey(String postCode, String addr, String town)
        {
            const string getAllAddrQuery = @"SELECT Address.AddressID,Address.Address, Address.PostCode, Town.TownName,County.CountyName, Country.CountryName
                                            FROM    Address INNER JOIN
                                                    Country ON Address.CountryID = Country.CountryID INNER JOIN
                                                    County ON Address.CountyID = County.CountyID AND Country.CountryID = County.CountryID INNER JOIN
                                                    Town ON Address.TownID = Town.TownID AND County.CountyID = Town.CountyID 
                                                    WHERE Address.PostCode  like '%'+@PostCode+'%' AND Address.Address like '%'+@Address+'%' AND Town.TownName like '%'+@Town+'%' ";


            List<Address> listAddr = new List<Address>();

            using (var helper = new SqlHelper())
            {
                SqlParameter[] sqlParameters = new SqlParameter[3];
                sqlParameters[0] = new SqlParameter { ParameterName = "@PostCode", Value = postCode };
                sqlParameters[1] = new SqlParameter { ParameterName = "@Address", Value = addr };
                sqlParameters[2] = new SqlParameter { ParameterName = "@Town", Value = town };
                var reader = helper.ExecuteReader(getAllAddrQuery, sqlParameters);
                while (reader.Read())
                {
                    var towns = new Town(reader.GetString(3));
                    var county = new County(reader.GetString(4));
                    var country = new Country(reader.GetString(5));
                    var address = new Address(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), towns, county, country);
                    listAddr.Add(address);
                }
            }

            return listAddr;   
        }

        public bool addAddress(Address address)
        {
            bool reply = false;
            const string insertAddrQuery = @"INSERT INTO Address (PostCode,TownID,CountyID,CountryID,[Address]) VALUES (@PostCode, @TownID, @CountyID, @CountryID,@Address)";
            using (var helper = new SqlHelper())
            {
                SqlParameter[] sqlParameters = new SqlParameter[5];
                sqlParameters[0] = new SqlParameter { ParameterName = "@PostCode", Value = address.PostCode };
                sqlParameters[1] = new SqlParameter { ParameterName = "@TownID", Value = address._Town.TownId };
                sqlParameters[2] = new SqlParameter { ParameterName = "@CountyID", Value = address._County.CountyId };
                sqlParameters[3] = new SqlParameter { ParameterName = "@CountryID", Value = address._Country.CountryId };
                sqlParameters[4] = new SqlParameter { ParameterName = "@Address", Value = address._Address };

                reply = helper.ExecuteInsertQuery(insertAddrQuery, sqlParameters);
            }

            return reply;
        }

        public Address getById(int id)
        {
            Address addr;
            const string getAddbyId = @"select AddressID,[Address],PostCode,TownID,CountyID,CountryID FROM Address Where AddressID=@AddressId";
            SqlParameter[] sqlParameter = new SqlParameter[1];
            sqlParameter[0] = new SqlParameter { ParameterName = "@AddressId", Value = id };
            using (var helper = new SqlHelper())
            {
                var reader = helper.ExecuteReader(getAddbyId, sqlParameter);
                if (reader.Read())
                {
                    var towns = new Town { TownId = reader.GetInt32(3)};                   
                    var county = new County { CountyId = reader.GetInt32(4)};
                    var country = new Country { CountryId = reader.GetInt32(5)};                    
                    addr = new Address(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), towns, county, country);
                }                    
                else
                    addr = null;
            }
            return addr;            
        }

        public List<Address> getByName(string name)
        {
	        
	        return null;
        }

        public List<Address> getAllAddress() 
        {
            const string getAllAddrQuery = @"SELECT        Address.AddressID,Address.Address, Address.PostCode, Town.TownName,County.CountyName, Country.CountryName
                                             FROM          Address INNER JOIN
                                                           Country ON Address.CountryID = Country.CountryID INNER JOIN
                                                           County ON Address.CountyID = County.CountyID AND Country.CountryID = County.CountryID INNER JOIN
                                                           Town ON Address.TownID = Town.TownID AND County.CountyID = Town.CountyID";
            List<Address> listAddr = new List<Address>();

            using (var helper = new SqlHelper())
            {
                var reader = helper.ExecuteReader(getAllAddrQuery,null);
                while (reader.Read())
                {                    
                    var towns = new Town(reader.GetString(3));
                    var county = new County(reader.GetString(4));
                    var country = new Country(reader.GetString(5));
                    var address = new Address(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), towns, county, country);
                    listAddr.Add(address);
                }                     
            }

            return listAddr;            
        }

        public bool updateAddress(Address address)  
        {
            bool reply = false;
            const string updateAddrQuery = @"UPDATE Address SET PostCode = @PostCode,
                                                                TownID = @TownID ,
                                                                CountyID = @CountyID,
                                                                CountryID = @CountryID,
                                                                [Address]=@Address 
                                            WHERE AddressId=@AddressId";
            using (var helper = new SqlHelper())
            {
                SqlParameter[] sqlParameters = new SqlParameter[6];
                sqlParameters[0] = new SqlParameter { ParameterName = "@PostCode", Value = address.PostCode };
                sqlParameters[1] = new SqlParameter { ParameterName = "@TownID", Value = address._Town.TownId };
                sqlParameters[2] = new SqlParameter { ParameterName = "@CountyID", Value = address._County.CountyId };
                sqlParameters[3] = new SqlParameter { ParameterName = "@CountryID", Value = address._Country.CountryId };
                sqlParameters[4] = new SqlParameter { ParameterName = "@Address", Value = address._Address };
                sqlParameters[5] = new SqlParameter { ParameterName = "@AddressId", Value = address.AddressId };

                reply = helper.ExecuteUpdateQuery(updateAddrQuery, sqlParameters);
            }

            return reply;
        }
    }
}